﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Net.Studio.Forms;
using Net.Orm;
using System.Data;

namespace DataModelWF
{
    public class DBData : IData
    {
        public DBModel Load(string s)
        {
            DBModel dbModel=new DBModel();
            try
            {
                DataSet rmdata;
                DBHelper db;

                if (!string.IsNullOrEmpty(s))
                {
                    string ConnStr = System.Configuration.ConfigurationManager.ConnectionStrings[s].ToString();
                    db = new DBHelper(ConnStr);
                }
                else
                {
                    db = new DBHelper();
                } 
               
                string sql = string.Format(@"SELECT id,name as tablename FROM  sysobjects Where xtype='U' AND  name <> 'dtproperties' ORDER BY id;
                                        SELECT d.name as tablename,a.colorder AS number,a.name AS cloumn ,       
                                        (CASE WHEN ( SELECT    COUNT(*)  FROM sysobjects 
                                        WHERE(name IN (SELECT name FROM sysindexes  WHERE ( id = a.id ) 
                                        AND ( indid IN (SELECT indid FROM  sysindexkeys 
                                        WHERE ( id = a.id ) AND ( colid IN (SELECT colid FROM syscolumns WHERE ( id = a.id )  
                                        AND ( name = a.name ) ) ) ) ) ) ) AND ( xtype = 'PK' )) > 0 THEN '1'  ELSE '0' END ) AS ispk , 
                                        b.name AS typename ,COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS Leng ,
                                        ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS digits,
                                        ( CASE WHEN a.isnullable = 1 THEN '1' ELSE '0' END ) AS isnullable,
                                        ISNULL(e.text, '') AS defaultval,ISNULL(g.[value], '') AS remark 
                                        FROM syscolumns a 
                                        LEFT JOIN systypes b ON a.xtype = b.xusertype 
                                        INNER JOIN sysobjects d ON a.id = d.id 
                                        AND d.xtype = 'U' AND d.name <> 'dtproperties' 
                                        LEFT JOIN syscomments e ON a.cdefault = e.id 
                                        LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id 
                                        ORDER BY a.id,a.colorder; 
                                        select indexs.Tab_Name as tablename ,indexs.Index_Name as indexname ,indexs.[Co_Names] as indexcolumns,
                                         Convert(varchar(2),Ind_Attribute.is_primary_key) as ispk,Convert(varchar(2),Ind_Attribute.is_unique) AS isunique,
                                         Convert(varchar(2),Ind_Attribute.is_disabled) AS isdisabled from (
                                         select Tab_Name,Index_Name, [Co_Names]=stuff((select ','+[Co_Name] from
                                         ( select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind
                                         inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
                                         inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
                                         inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
                                         ) t where Tab_Name=tb.Tab_Name and Index_Name=tb.Index_Name for xml path('')), 1, 1, '')
                                         from (select tab.Name as Tab_Name,ind.Name as Index_Name,Col.Name as Co_Name from sys.indexes ind
                                         inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
                                         inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
                                         inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id)tb
                                         where Tab_Name not like 'sys%'
                                         group by Tab_Name,Index_Name
                                         ) indexs inner join sys.indexes Ind_Attribute on indexs.Index_Name = Ind_Attribute.name
                                         order by indexs.Tab_Name");

                rmdata = db.Search(sql);

                //if (!string.IsNullOrEmpty(s))
                //{
                //     DbSession dbs = new DbSession(s); 
                //     DataFactory.remote = dbs;
                //     rmdata = DataFactory.remote.FromSql(sql).ToDataSet().Data; 
                //}
                //else
                //{
                //     rmdata = DataFactory.local.FromSql(sql).ToDataSet().Data;
                //}

                if (rmdata.Tables.Count == 0 || rmdata.Tables.Count < 3)
                {
                    return null;
                }
                 
                DataTable dtTB = rmdata.Tables[0];
                DataTable dtFD = rmdata.Tables[1];
                DataTable dtTR = rmdata.Tables[2];
                 
                Dictionary<string, TableModel> dicTable = new Dictionary<string, TableModel>();

                foreach (DataRow rt in dtTB.Rows)
                {
                    TableModel TM = new TableModel(string.Format("{0},{0}", rt["tablename"].ToString()));  
                    Dictionary<string, TableModelField> dicFields = new Dictionary<string, TableModelField>();
                    Dictionary<string, TableModelRelation> dicRelations = new Dictionary<string, TableModelRelation>();


                    //关系信息
                    int sortNo = 1;
                    foreach (DataRow rr in dtTR.Rows)
                    {
                        if (rr["tablename"].ToString() == rt["tablename"].ToString())
                        {
                            TableModelRelation tmr = new TableModelRelation()
                            {
                                SortNo = sortNo,
                                Id = Guid.NewGuid().ToString(),
                                DTRNo = rr["indexname"].ToString(),
                                DTRType = (rr["ispk"].ToString() == rr["ispk"].ToString()) ? "PK" : "AK",
                                FiledNos = rr["indexcolumns"].ToString(),
                                FKFiledNos = rr["indexcolumns"].ToString()
                            };
                            dicRelations.Add(tmr.DTRNo, tmr);
                            sortNo++;
                        }
                    }
                     
                    //字段信息
                    foreach (DataRow rf in dtFD.Rows)
                    {
                        if (rf["tablename"].ToString() == rt["tablename"].ToString())
                        {
                            TableModelField tmf = new TableModelField() {
                                SortNo = Convert.ToInt32(rf["number"].ToString()),
                                Id = Guid.NewGuid().ToString(),
                                DTFNo = rf["cloumn"].ToString(),
                                DTFName = rf["remark"].ToString(),
                                FullDTFType = rf["typename"].ToString(),
                                DataLength = (rf["leng"].ToString() == "-1") ? "max" : rf["leng"].ToString(),
                                DecLength = rf["digits"].ToString(),
                                DefaultValue = rf["defaultval"].ToString(),
                                IsNotNull = rf["isnullable"].ToString(),
                                IndentKey = IsPKOrIndexKey(rf["cloumn"].ToString(), dicRelations, false) ? "1" : "0",
                                PrimaryKey = IsPKOrIndexKey(rf["cloumn"].ToString(),dicRelations,true)?"1":"0"
                            };
                            dicFields.Add(tmf.DTFNo,tmf);
                             
                        }
                    }

                    TM.Relations = dicRelations;
                    TM.Fields = dicFields; 
                    dicTable.Add(TM.No, TM); 
                }
                  
                dbModel.Tables = dicTable;  
            }
            catch (Exception ex)
            {
                return null;
            } 
            return dbModel;
        }

        

        public bool Save(DBModel db)
        { 
            try
            {
                //源
                if (db == null||db.Tables.Count==0) return false; 

                //目标
                DBModel dbTraget = Load(string.Empty);
                if (dbTraget == null || dbTraget.Tables.Count == 0) return false;

                bool isType = true; //以目标为基准，否则以源为基准
                Dictionary<string, string> dicAllQuery = new Dictionary<string, string>();
                bool qflg = true;
                DBModel dbModel = new DBModel();

                foreach (string tb in db.Tables.Keys)
                {
                    TableModel tbModelBase = db.Tables[tb];
                    TableModel tbModel = null;
                    if (dbTraget.Tables.Keys.Contains(tb)) { tbModel = dbTraget.Tables[tb];  }

                    TableModel tableModel = MergeTableModel(tbModelBase, tbModel, isType);

                    if (tableModel == null|| tableModel.sqlExcuteType==SQLExcuteType.NO) continue; 

                    dicAllQuery.Add(tb, tableModel.StrSQL);
                    dbModel.Tables[tb] = tableModel;
                }

                DBHelper dbhelper = new DBHelper();

                dbhelper.BeginTran();
                foreach (string key in dicAllQuery.Keys)
                {
                    bool iret = dbhelper.Query(dicAllQuery[key]);
                    if (!iret)
                    {
                        qflg = false;
                        break;
                    }
                }
                if (!qflg)
                {
                    dbhelper.RollbackTran();
                }
                dbhelper.Commit();



                //#region 合并处理 
                //Dictionary<string, string> dicAllQuery = DBModelQuery(dbTraget, db, isType);
                //bool qflg = true;
                //DataFactory.local.CreateTran();
                //foreach (string key in dicAllQuery.Keys)
                //{ 
                //    int iret = DataFactory.local.FromSql(dicAllQuery[key]).ToNonQuery().Data;
                //    if (iret <= 0)
                //    {
                //        qflg = false;
                //        break;
                //    } 
                //}
                //if (!qflg)
                //{
                //    DataFactory.local.RollbackTran();
                //}
                //DataFactory.local.CommitTran();
                //#endregion 
            }
            catch (Exception ex)
            {
                return false;
            } 
            return true; 
        }


       

        #region  合并TableModel
        /// <summary>
        /// 合并
        /// </summary>
        /// <param name="tmBase">源数据</param>
        /// <param name="tm">目标数据</param>
        /// <param name="isType">以目标为基准，否则以源为基准</param>
        /// <returns></returns>
        public TableModel MergeTableModel(TableModel tmBase,TableModel tm,bool isType)
        {
            TableModel tbModel=null;
            TableModel tableModelBase = null;
            TableModel tableModel = null;
            try
            {
                if (tmBase == null && tm == null) return tbModel; 
                tableModelBase = tmBase;
                tableModel = tm;
                 
                if (isType) {
                    if (tableModelBase != null && tableModel == null)
                    {
                        //create
                        tbModel = CreateTBQuery(tableModelBase); 
                    }

                    if (tableModelBase != null && tableModel != null)
                    {
                        //alter
                        tbModel = AlterTBQuery(tableModelBase,tableModel,true);
                    }

                    if(tableModelBase == null && tableModel != null)
                    {
                        //不操作
                        tbModel = tableModel;
                        tbModel.sqlExcuteType = SQLExcuteType.NO;
                    }
                }
                else
                {
                    if (tableModelBase == null && tableModel != null)
                    {
                        //drop
                        tbModel = DropTBQuery(tableModel);
                    }
                    if (tableModelBase != null && tableModel != null)
                    {
                        //alter
                        tbModel = AlterTBQuery(tableModelBase, tableModel, false);
                    }
                    if (tableModelBase != null && tableModel == null)
                    {
                        //create
                        tbModel = CreateTBQuery(tableModelBase);
                    }
                }
                
            }
            catch(Exception ex)
            {
                return null;
            }
            return tbModel;
        }


        /// <summary>
        /// 构建创建表sql
        /// </summary>
        /// <param name="tableModel"></param>
        /// <returns>返回值存在null</returns>
        private TableModel CreateTBQuery(TableModel tableModel)
        {
            if (tableModel == null) return null;
            TableModel tm = tableModel;

            Dictionary<string, TableModelField> dicFields = tableModel.Fields;
            Dictionary<string, TableModelRelation> dicRelations = tableModel.Relations;
            string sqlfields = string.Empty, sqlrelation = string.Empty, sqlalter = string.Empty, query = string.Empty;

            foreach (string f in dicFields.Keys)
            {
                //字段构建
                sqlfields += string.Format(" \r\n [{0}] [{1}]{2} {3}NULL,", dicFields[f].DTFNo, dicFields[f].FullDTFType, FieldDataLength(dicFields[f]), dicFields[f].IsNotNull == "0" ? "NOT " : "");

                //字段默认值
                if (!string.IsNullOrWhiteSpace(dicFields[f].DefaultValue))
                {
                    sqlalter += string.Format("ALTER TABLE [dbo].[{0}] ADD  CONSTRAINT [DF_{0}_{2}] DEFAULT {1} FOR [{2}]\r\n GO\r\n", tm.No, dicFields[f].DefaultValue, dicFields[f].DTFNo);
                }

                //备注说明
                if (!string.IsNullOrWhiteSpace(dicFields[f].DTFName))
                {
                    sqlalter += string.Format("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{0}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{1}', @level2type=N'COLUMN',@level2name=N'{2}'\r\n GO\r\n",
                        dicFields[f].DTFName, tm.No, dicFields[f].DTFNo);
                }
            }

            if (!string.IsNullOrEmpty(sqlfields))
            {
                sqlfields = sqlfields.TrimEnd(',');
            }

            string strPK = string.Empty, strAK = string.Empty;
            foreach (string r in dicRelations.Keys)
            {
                string strASC = string.Format("[{0}]", dicRelations[r].FiledNos);
                if (strASC.IndexOf(",") != -1)
                {
                    strASC = string.Empty;
                    List<string> lsCode = dicRelations[r].FiledNos.Split(',').ToList();
                    foreach (string n in lsCode)
                    {
                        strASC += string.Format(" \r\n [{0}] ASC,", n);
                    }
                    strASC = strASC.TrimEnd(',');
                }

                if (dicRelations[r].DTRType == "PK")
                {
                    strPK += string.Format("\r\n CONSTRAINT [{0}] PRIMARY KEY CLUSTERED \r\n ({1}\r\n )WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY],", dicRelations[r].DTRNo, strASC);
                }
                else
                {
                    strAK += string.Format("\r\n CONSTRAINT [{0}] UNIQUE NONCLUSTERED \r\n ({1}\r\n )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],", dicRelations[r].DTRNo, strASC);
                }
            }

            if (!string.IsNullOrEmpty(strPK))
            {
                strPK = strPK.TrimEnd(',');
                sqlrelation = strPK;
            }

            if (!string.IsNullOrEmpty(strAK))
            {
                strAK = strAK.TrimEnd(',');
                sqlrelation += string.Format("{0}{1}", string.IsNullOrEmpty(sqlrelation) ? "" : ",", strAK);
            }

            query = string.Format("\r\n --------------------------------------------创建表：{0}-开始------------------------------------------------\r\n SET ANSI_PADDING ON\r\n GO\r\n CREATE TABLE [dbo].[{0}]({1}{2}) ON [PRIMARY]\r\n GO\r\n SET ANSI_PADDING OFF\r\n GO\r\n {3}\r\n --------------------------------------------创建表：{0}-结束------------------------------------------------\r\n", tm.No, sqlfields,
                string.Format("{0}{1}", string.IsNullOrEmpty(sqlrelation) ? "" : ",", sqlrelation), sqlalter);

            tableModel.sqlExcuteType = SQLExcuteType.Create;
            tableModel.StrSQL = query; 
            return tableModel; 
        }


        /// <summary>
        /// 构建修改表sql
        /// </summary>
        /// <param name="tableModelBase">源数据</param>
        /// <param name="tableModel">目标数据</param>
        /// <param name="isType">以目标为基准，否则以源为基准</param>
        /// <returns></returns>
        private TableModel AlterTBQuery(TableModel tableModelBase, TableModel tableModel, bool isType)
        {
            TableModel tm = tableModel;
            TableModel tmBase = tableModelBase;
            if (tm == null || tm.Fields.Count == 0 || tmBase == null || tmBase.Fields.Count == 0 || tm.No != tmBase.No)
            {
                return null;
            }

            string key = tableModelBase.No;

            Dictionary<string, TableModelField> dicFields = tableModel.Fields;
            Dictionary<string, TableModelRelation> dicRelations = tableModel.Relations;

            Dictionary<string, TableModelField> dicFieldsBase = tableModelBase.Fields;
            Dictionary<string, TableModelRelation> dicRelationsBase = tableModelBase.Relations;
            //定义变量
            string sqlalter = string.Empty, query = string.Empty;

            #region 分离处理
            //分离当前表字段/关系哪些是需要新增，修改，删除
            List<string> lsFadd = new List<string>();
            List<string> lsFupd = new List<string>();
            List<string> lsFdel = new List<string>();

            List<string> lsRadd = new List<string>();
            List<string> lsRupd = new List<string>();
            List<string> lsRdel = new List<string>();


            //字段
            foreach (string fkey in dicFields.Keys)
            {
                bool fflg = false;
                foreach (string bfkey in dicFieldsBase.Keys)
                {
                    if (fkey == bfkey)
                    {
                        lsFupd.Add(fkey);
                        fflg = true;
                        break;
                    }
                }
                if (!fflg)
                {
                    lsFadd.Add(fkey);
                }
            }

            //索引
            foreach (string fkey in dicRelations.Keys)
            {
                bool fflg = false;
                foreach (string bfkey in dicRelationsBase.Keys)
                {
                    if (fkey == bfkey)
                    {
                        lsRupd.Add(fkey);
                        fflg = true;
                        break;
                    }
                }
                if (!fflg)
                {
                    lsRadd.Add(fkey);
                }
            }


            //以源为基准
            if (!isType)
            {
                //字段
                foreach (string fkey in dicFieldsBase.Keys)
                {
                    bool fflg = false;
                    foreach (string bfkey in dicFields.Keys)
                    {
                        if (fkey == bfkey)
                        {
                            fflg = true;
                            break;
                        }
                    }
                    if (!fflg)
                    {
                        lsFdel.Add(fkey);
                    }
                }

                //索引
                foreach (string fkey in dicRelationsBase.Keys)
                {
                    bool fflg = false;
                    foreach (string bfkey in dicRelations.Keys)
                    {
                        if (fkey == bfkey)
                        {
                            fflg = true;
                            break;
                        }
                    }
                    if (!fflg)
                    {
                        lsRdel.Add(fkey);
                    }
                }
            }
            #endregion

            //后续处理顺序
            #region 新增字段处理 
            //ALTER TABLE[表名] ADD[字段名] NVARCHAR(50) NULL
            //GO
            //ALTER TABLE [dbo].[表名] ADD  DEFAULT (newid()) FOR [字段名]
            //GO 
            //EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'功能名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SY_Function', @level2type=N'COLUMN',@level2name=N'FunName'
            //GO
            foreach (string fdkey in lsFadd)
            {
                TableModelField tmf = dicFields[fdkey];
                if (tmf == null) continue;

                //增加列
                //(tmf.FullDTFType == "datetime") ? "" : string.Format("({0})", tmf.DecLength == "0" ? tmf.DataLength : string.Format("{0},{1}", tmf.DataLength, tmf.DecLength))
                sqlalter += string.Format("ALTER TABLE[{0}] ADD[{1}] {2}{3} {4}NULL\r\n GO\r\n", key, tmf.DTFNo, tmf.FullDTFType, FieldDataLength(tmf), tmf.IsNotNull == "0" ? "NOT " : "");

                //字段默认值
                if (!string.IsNullOrEmpty(tmf.DefaultValue))
                {
                    sqlalter += string.Format("ALTER TABLE [dbo].[{0}] ADD CONSTRAINT [DF_{0}_{2}] DEFAULT {1} FOR [{2}]\r\n GO\r\n", key, tmf.DefaultValue, tmf.DTFNo);
                }

                //备注说明
                if (!string.IsNullOrEmpty(tmf.DTFName))
                {
                    sqlalter += string.Format("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{0}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{1}', @level2type=N'COLUMN',@level2name=N'{2}'\r\n GO\r\n",
                        tmf.DTFName, key, tmf.DTFNo);
                }
            }

            #endregion

            #region 更改字段处理
            //属性更改，字段类型，长度，是否为null, 默认值，备注
            //ALTER TABLE [表名] ALTER COLUMN [字段名] VARCHAR (50) NULL
            //GO
            //ALTER TABLE [dbo].[表名] ADD  DEFAULT (newid()) FOR [字段名]
            //GO 
            //EXEC sys.sp_addextendedproperty/sp_updateextendedproperty @name=N'MS_Description', @value=N'功能名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SY_Function', @level2type=N'COLUMN',@level2name=N'FunName'
            //GO
            foreach (string fdkey in lsFupd)
            {
                TableModelField tmf = dicFields[fdkey];
                TableModelField tmfBase = dicFieldsBase[fdkey];
                if (tmf == null || tmfBase == null) continue;
                //没有任何属性改变
                if (tmf.FullDTFType == tmfBase.FullDTFType && tmf.DataLength == tmfBase.DataLength && tmf.DecLength == tmfBase.DecLength && tmf.IsNotNull == tmfBase.IsNotNull && tmf.DefaultValue == tmfBase.DefaultValue && tmf.DTFName == tmfBase.DTFName)
                {
                    continue;
                }
                //改变了类型，长度，是否为null
                if (tmf.FullDTFType != tmfBase.FullDTFType || tmf.DataLength != tmfBase.DataLength || tmf.DecLength != tmfBase.DecLength || tmf.IsNotNull != tmfBase.IsNotNull)
                {
                    //修改列属性
                    //(tmf.FullDTFType == "datetime") ? "" : string.Format("({0})", tmf.DecLength == "0" ? tmf.DataLength : string.Format("{0},{1}", tmf.DataLength, tmf.DecLength))
                    sqlalter += string.Format("ALTER TABLE[{0}] ALTER COLUMN [{1}] {2}{3} {4}NULL\r\n GO\r\n", key, tmf.DTFNo, tmf.FullDTFType, FieldDataLength(tmf), tmf.IsNotNull == "0" ? "NOT " : "");
                }

                //字段默认值
                if (tmf.DefaultValue != tmfBase.DefaultValue)
                {
                    //ALTER TABLE [SY_Function] drop constraint [DF_SY_Function_SortNo]
                    // ALTER TABLE[SY_Function] ADD CONSTRAINT [DF_SY_Function_SortNo]  DEFAULT((2)) FOR[SortNo]
                    if (!string.IsNullOrEmpty(tmfBase.DefaultValue))
                    {
                        sqlalter += string.Format("ALTER TABLE [{0}] drop constraint [DF_{0}_{1}]\r\n GO\r\n", key, tmf.DTFNo);
                    }
                    if (!string.IsNullOrEmpty(tmf.DefaultValue))
                    {
                        sqlalter += string.Format("ALTER TABLE [dbo].[{0}] ADD CONSTRAINT [DF_{0}_{2}] DEFAULT {1} FOR [{2}]\r\n GO\r\n", key, tmf.DefaultValue, tmf.DTFNo);
                    }
                }

                //备注说明
                if (tmf.DTFName != tmfBase.DTFName)
                {
                    sqlalter += string.Format("EXEC sys.{3} @name=N'MS_Description', @value=N'{0}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{1}', @level2type=N'COLUMN',@level2name=N'{2}'\r\n GO\r\n",
                        tmf.DTFName, key, tmf.DTFNo, string.IsNullOrEmpty(tmfBase.DTFName) ? "sp_addextendedproperty" : "sp_updateextendedproperty");
                }
            }
            #endregion

            #region 新增索引处理
            foreach (string fdrkey in lsRadd)
            {
                TableModelRelation tmr = dicRelations[fdrkey];
                if (tmr == null) continue;

                //索引名称不同，索引字段相同并且是相同索引类型，不执行
                bool rflg = false;
                foreach (string bpk in dicRelationsBase.Keys)
                {
                    if (tmr.FiledNos == dicRelationsBase[bpk].FiledNos && dicRelationsBase[bpk].DTRType == tmr.DTRType && tmr.DTRNo != dicRelationsBase[bpk].DTRNo)
                    {
                        rflg = true;
                        break;
                    }
                }
                if (rflg) continue;


                //字段里存在多个处理
                string strFD = string.Format("[{0}]", tmr.FiledNos);
                if (strFD.IndexOf(",") != -1)
                {
                    strFD = string.Empty;
                    List<string> lsRCode = tmr.FiledNos.Split(',').ToList();
                    foreach (string n in lsRCode)
                    {
                        strFD += string.Format("[{0}],", n);
                    }
                    strFD = strFD.TrimEnd(',');
                }

                //索引创建
                if (tmr.DTRType == "PK")
                {
                    sqlalter += string.Format("ALTER TABLE {0} ADD CONSTRAINT [{1}] PRIMARY KEY ({2})\r\n GO\r\n", key, tmr.DTRNo, strFD);
                }
                else
                {
                    sqlalter += string.Format("CREATE UNIQUE INDEX {0} ON [{1}]({2})\r\n GO\r\n", tmr.DTRNo, key, strFD);
                }
            }
            #endregion

            #region 更改索引处理

            foreach (string fdrkey in lsRupd)
            {
                TableModelRelation tmr = dicRelations[fdrkey];
                if (tmr == null) continue;

                //索引名称相同，索引字段相同并且是相同索引类型，不执行
                bool rflg = false;
                foreach (string bpk in dicRelationsBase.Keys)
                {
                    if (tmr.FiledNos == dicRelationsBase[bpk].FiledNos && dicRelationsBase[bpk].DTRType == tmr.DTRType && tmr.DTRNo == dicRelationsBase[bpk].DTRNo)
                    {
                        rflg = true;
                        break;
                    }
                }
                if (rflg) continue;


                //字段里存在多个处理
                string strFD = string.Format("[{0}]", tmr.FiledNos);
                if (strFD.IndexOf(",") != -1)
                {
                    strFD = string.Empty;
                    List<string> lsRCode = tmr.FiledNos.Split(',').ToList();
                    foreach (string n in lsRCode)
                    {
                        strFD += string.Format("[{0}],", n);
                    }
                    strFD = strFD.TrimEnd(',');
                }

                //todo 
                //先删后增索引
                sqlalter += string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]\r\n GO\r\n", key, tmr.DTRNo);
                if (tmr.DTRType == "PK")
                {
                    sqlalter += string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] PRIMARY KEY ({2})\r\n GO\r\n", key, tmr.DTRNo, strFD);
                }
                else
                {
                    sqlalter += string.Format("CREATE UNIQUE INDEX [{0}] ON [{1}]({2})\r\n GO\r\n", tmr.DTRNo, key, strFD);
                }
            }

            #endregion

            #region 删除索引处理
            foreach (string fdrkey in lsRdel)
            {
                sqlalter += string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]\r\n GO\r\n", key, fdrkey);
            }

            #endregion

            #region 删除字段处理
            foreach (string fdkey in lsFdel)
            {
                sqlalter += string.Format("ALTER TABLE [{0}] DROP COLUMN [{1}]\r\n GO\r\n", key, fdkey);
            }
            #endregion

            if (string.IsNullOrEmpty(sqlalter))
            {
                tableModelBase.sqlExcuteType = SQLExcuteType.NO;
                return tableModelBase;
            }
            query = string.Format("\r\n --------------------------------------------修改表：{1}-开始------------------------------------------------\r\n SET ANSI_PADDING ON\r\n GO\r\n {0}\r\n GO\r\n SET ANSI_PADDING OFF\r\n GO\r\n --------------------------------------------修改表：{1}-结束------------------------------------------------\r\n", sqlalter,key);

            tableModelBase.sqlExcuteType = SQLExcuteType.Alter;
            tableModelBase.StrSQL = query;

            return tableModelBase;

        }

        /// <summary>
        /// 构建删除表sql
        /// </summary>
        /// <param name="tableModel"></param>
        /// <returns>返回值存在null</returns>
        private TableModel DropTBQuery(TableModel tableModel)
        {
            if (tableModel == null) return null;
            string query = string.Format("\r\n --------------------------------------------删除表：{0}-开始------------------------------------------------\r\n IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U'))\r\n DROP TABLE [dbo].[{0}]\r\n GO\r\n --------------------------------------------删除表：{0}-结束------------------------------------------------\r\n", tableModel.No);
            tableModel.StrSQL = query;
            tableModel.sqlExcuteType = SQLExcuteType.Drop;
            return tableModel;
        }

        #endregion



        /// <summary>
        /// 查找是否是索引或者主键
        /// </summary>
        /// <param name="DTFNo"></param>
        /// <param name="dic"></param>
        /// <param name="isPK"></param>
        /// <returns></returns>
        private bool IsPKOrIndexKey(string DTFNo, Dictionary<string, TableModelRelation> dic, bool isPK)
        {
            if (dic == null || dic.Keys.Count == 0 || string.IsNullOrEmpty(DTFNo)) return false;
            bool flg = false;
            foreach (string k in dic.Keys)
            {
                string filedNodes = dic[k].FiledNos;
                if (isPK)
                {
                    if (filedNodes == DTFNo && dic[k].DTRType == "PK")
                    {
                        flg = true;
                        break;
                    }
                }
                else
                {
                    string[] strItem = filedNodes.Split(',');
                    if (strItem.ToList().Any(v => v == DTFNo) && dic[k].DTRType == "AK")
                    {
                        flg = true;
                        break;
                    }
                }
            }
            return flg;
        }

        /// <summary>
        /// 类型长度构建
        /// </summary>
        /// <param name="tmf"></param>
        /// <returns></returns>
        private string FieldDataLength(TableModelField tmf)
        {
            string str = string.Empty;
            List<string> lsTypes = new List<string>() { "char", "nchar", "varchar", "nvarchar", "binary", "vbinary", "time", "datetimeoffset", "datetime2", "numeric", "decima" };
            if (!lsTypes.Any(s => s == tmf.FullDTFType))
            {
                return str;
            }

            if (tmf.FullDTFType == "numeric" || tmf.FullDTFType == "decima")
            {
                str = string.Format("({0},{1})", tmf.DataLength, tmf.DecLength);
            }
            else
            {
                str = string.Format("({0})", tmf.DataLength);
            }
            return str;
        }

        #region 两个数据库比较最终返回sql
        /// <summary>
        /// 两个数据库比较最终返回sql
        /// </summary>
        /// <param name="dbModelBase">目标</param>
        /// <param name="dbModel">源</param>
        /// <param name="isType">//true 以目标为基准，否则以源为基准</param>
        /// <returns></returns>
        private Dictionary<string, string> DBModelQuery(DBModel dbModelBase, DBModel dbModel, bool isType)
        {
            Dictionary<string, string> dicAllQuery = new Dictionary<string, string>();
            try
            {
                List<String> lsadd = new List<string>();     //新增的表
                List<String> lsupdate = new List<string>();  //更新的表
                List<String> lsdel = new List<string>();     //删除的表 

                foreach (string key in dbModel.Tables.Keys)
                {
                    bool flg = false;
                    foreach (string k in dbModelBase.Tables.Keys)
                    {
                        if (key == k)
                        {
                            flg = true;
                            lsupdate.Add(k);
                            break;
                        }
                    }
                    if (!flg)
                    {
                        lsadd.Add(key);
                    }
                }


                if (!isType)
                {
                    foreach (string key in dbModelBase.Tables.Keys)
                    {
                        bool flg = false;
                        foreach (string k in dbModel.Tables.Keys)
                        {
                            if (key == k)
                            {
                                flg = true;
                                break;
                            }
                        }
                        if (!flg)
                        {
                            lsdel.Add(key);
                        }
                    }
                }

                //处理新增表，修改表结构，删除表 
                Dictionary<string, string> dicAddQuery = new Dictionary<string, string>();
                Dictionary<string, string> dicUpdateQuery = new Dictionary<string, string>();
                Dictionary<string, string> dicDelQuery = new Dictionary<string, string>();

                #region 构建SQL语句

                if (lsadd.Count > 0)
                {
                    dicAddQuery = CreateTBQuery(lsadd, dbModel);
                }

                if (lsupdate.Count > 0)
                {
                    dicUpdateQuery = AlterTBQuery(lsupdate, dbModelBase, dbModel, isType);
                }

                if (lsdel.Count > 0)
                {
                    dicDelQuery = DropTBQuery(lsdel);
                }

                #endregion

                dicAllQuery = dicAddQuery.Union(dicUpdateQuery).Union(dicDelQuery).ToDictionary(k => k.Key, v => v.Value);
                //string strsql=string.Format("{0}",String.Join("\r\nGO\r\n",dicAllQuery.Values.ToArray()));

            }
            catch (Exception ex)
            {

            }
            return dicAllQuery;
        }


        /// <summary>
        /// 修改表信息字段，默认值，备注，索引
        /// </summary>
        /// <param name="lstb"></param>
        /// <param name="dbModelBase"></param>
        /// <param name="dbModel"></param>
        /// <param name="isType">true 是目标为基准</param>
        /// <returns></returns>
        private Dictionary<string, string> AlterTBQuery(List<string> lstb, DBModel dbModelBase, DBModel dbModel,bool isType)
        {
            Dictionary<string, string> dicquery = new Dictionary<string, string>();
            int index = 1;
            foreach (string key in lstb)
            {
                TableModel tm = dbModel.Tables[key];
                TableModel tmBase = dbModelBase.Tables[key];
                if (tm == null ||tm.Fields.Count==0 || tmBase ==null||tmBase.Fields.Count==0)
                {
                    continue;
                }

                Dictionary<string, TableModelField> dicFields = dbModel.Tables[key].Fields;
                Dictionary<string, TableModelRelation> dicRelations = dbModel.Tables[key].Relations;

                Dictionary<string, TableModelField> dicFieldsBase = dbModelBase.Tables[key].Fields;
                Dictionary<string, TableModelRelation> dicRelationsBase = dbModelBase.Tables[key].Relations;
                //定义变量
                string sqlalter = string.Empty, query = string.Empty;

                #region 分离处理
                //分离当前表字段/关系哪些是需要新增，修改，删除
                List<string> lsFadd = new List<string>();
                List<string> lsFupd = new List<string>();
                List<string> lsFdel = new List<string>();

                List<string> lsRadd = new List<string>();
                List<string> lsRupd = new List<string>();
                List<string> lsRdel = new List<string>();


                //字段
                foreach(string fkey in dicFields.Keys)
                {
                    bool fflg = false;
                    foreach (string bfkey in dicFieldsBase.Keys)
                    {
                        if (fkey == bfkey)
                        {
                            lsFupd.Add(fkey);
                            fflg = true;
                            break;
                        }
                    }
                    if (!fflg)
                    {
                        lsFadd.Add(fkey);
                    }
                }

                //索引
                foreach (string fkey in dicRelations.Keys)
                {
                    bool fflg = false;
                    foreach (string bfkey in dicRelationsBase.Keys)
                    {
                        if (fkey == bfkey)
                        {
                            lsRupd.Add(fkey);
                            fflg = true;
                            break;
                        }
                    }
                    if (!fflg)
                    {
                        lsRadd.Add(fkey);
                    }
                }


                //以源为基准
                if (!isType)
                {
                    //字段
                    foreach (string fkey in dicFieldsBase.Keys)
                    {
                        bool fflg = false;
                        foreach (string bfkey in dicFields.Keys)
                        {
                            if (fkey == bfkey)
                            { 
                                fflg = true;
                                break;
                            }
                        }
                        if (!fflg)
                        {
                            lsFdel.Add(fkey);
                        }
                    }

                    //索引
                    foreach (string fkey in dicRelationsBase.Keys)
                    {
                        bool fflg = false;
                        foreach (string bfkey in dicRelations.Keys)
                        {
                            if (fkey == bfkey)
                            {
                                fflg = true;
                                break;
                            }
                        }
                        if (!fflg)
                        {
                            lsRdel.Add(fkey);
                        }
                    }
                }
                #endregion 

                //后续处理顺序
                #region 新增字段处理 
                //ALTER TABLE[表名] ADD[字段名] NVARCHAR(50) NULL
                //GO
                //ALTER TABLE [dbo].[表名] ADD  DEFAULT (newid()) FOR [字段名]
                //GO 
                //EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'功能名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SY_Function', @level2type=N'COLUMN',@level2name=N'FunName'
                //GO
                foreach (string fdkey in lsFadd)
                {
                    TableModelField tmf = dicFields[fdkey];
                    if (tmf == null) continue;

                    //增加列
                    //(tmf.FullDTFType == "datetime") ? "" : string.Format("({0})", tmf.DecLength == "0" ? tmf.DataLength : string.Format("{0},{1}", tmf.DataLength, tmf.DecLength))
                    sqlalter += string.Format("ALTER TABLE[{0}] ADD[{1}] {2}{3} {4}NULL\r\n GO\r\n", key, tmf.DTFNo, tmf.FullDTFType, FieldDataLength(tmf),tmf.IsNotNull == "0" ? "NOT " : "");

                    //字段默认值
                    if (!string.IsNullOrEmpty(tmf.DefaultValue))
                    {
                        sqlalter += string.Format("ALTER TABLE [dbo].[{0}] ADD CONSTRAINT [DF_{0}_{2}] DEFAULT {1} FOR [{2}]\r\n GO\r\n", key, tmf.DefaultValue, tmf.DTFNo);
                    }

                    //备注说明
                    if (!string.IsNullOrEmpty(tmf.DTFName))
                    {
                        sqlalter += string.Format("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{0}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{1}', @level2type=N'COLUMN',@level2name=N'{2}'\r\n GO\r\n",
                            tmf.DTFName, key, tmf.DTFNo);
                    } 
                }

                #endregion

                #region 更改字段处理
                //属性更改，字段类型，长度，是否为null, 默认值，备注
                //ALTER TABLE [表名] ALTER COLUMN [字段名] VARCHAR (50) NULL
                //GO
                //ALTER TABLE [dbo].[表名] ADD  DEFAULT (newid()) FOR [字段名]
                //GO 
                //EXEC sys.sp_addextendedproperty/sp_updateextendedproperty @name=N'MS_Description', @value=N'功能名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'SY_Function', @level2type=N'COLUMN',@level2name=N'FunName'
                //GO
                foreach (string fdkey in lsFupd)
                {
                    TableModelField tmf = dicFields[fdkey];
                    TableModelField tmfBase = dicFieldsBase[fdkey]; 
                    if (tmf == null||tmfBase==null) continue;
                    //没有任何属性改变
                    if (tmf.FullDTFType == tmfBase.FullDTFType && tmf.DataLength == tmfBase.DataLength && tmf.DecLength == tmfBase.DecLength && tmf.IsNotNull == tmfBase.IsNotNull && tmf.DefaultValue == tmfBase.DefaultValue && tmf.DTFName == tmfBase.DTFName)
                    {
                        continue;
                    }
                    //改变了类型，长度，是否为null
                    if (tmf.FullDTFType!=tmfBase.FullDTFType||tmf.DataLength!=tmfBase.DataLength || tmf.DecLength != tmfBase.DecLength || tmf.IsNotNull != tmfBase.IsNotNull)
                    {
                        //修改列属性
                        //(tmf.FullDTFType == "datetime") ? "" : string.Format("({0})", tmf.DecLength == "0" ? tmf.DataLength : string.Format("{0},{1}", tmf.DataLength, tmf.DecLength))
                        sqlalter += string.Format("ALTER TABLE[{0}] ALTER COLUMN [{1}] {2}{3} {4}NULL\r\n GO\r\n", key, tmf.DTFNo, tmf.FullDTFType,FieldDataLength(tmf),tmf.IsNotNull == "0" ? "NOT " : ""); 
                    }

                    //字段默认值
                    if (tmf.DefaultValue!=tmfBase.DefaultValue)
                    {
                        //ALTER TABLE [SY_Function] drop constraint [DF_SY_Function_SortNo]
                        // ALTER TABLE[SY_Function] ADD CONSTRAINT [DF_SY_Function_SortNo]  DEFAULT((2)) FOR[SortNo]
                        if (!string.IsNullOrEmpty(tmfBase.DefaultValue))
                        {
                            sqlalter += string.Format("ALTER TABLE [{0}] drop constraint [DF_{0}_{1}]\r\n GO\r\n", key,tmf.DTFNo);
                        }

                        if (!string.IsNullOrEmpty(tmf.DefaultValue))
                        {
                            sqlalter += string.Format("ALTER TABLE [dbo].[{0}] ADD CONSTRAINT [DF_{0}_{2}] DEFAULT {1} FOR [{2}]\r\n GO\r\n", key, tmf.DefaultValue, tmf.DTFNo);
                        }
                    }

                    //备注说明
                    if (tmf.DTFName != tmfBase.DTFName)
                    {
                        sqlalter += string.Format("EXEC sys.{3} @name=N'MS_Description', @value=N'{0}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{1}', @level2type=N'COLUMN',@level2name=N'{2}'\r\n GO\r\n",
                            tmf.DTFName, key, tmf.DTFNo,string.IsNullOrEmpty(tmfBase.DTFName)? "sp_addextendedproperty" : "sp_updateextendedproperty");
                    }
                }
                #endregion

                #region 新增索引处理
                foreach (string fdrkey in lsRadd)
                {
                    TableModelRelation tmr = dicRelations[fdrkey];
                    if (tmr == null) continue;

                    //索引名称不同，索引字段相同并且是相同索引类型，不执行
                    bool rflg = false;
                    foreach (string bpk in dicRelationsBase.Keys)
                    {
                        if (tmr.FiledNos == dicRelationsBase[bpk].FiledNos && dicRelationsBase[bpk].DTRType == tmr.DTRType && tmr.DTRNo != dicRelationsBase[bpk].DTRNo)
                        {
                            rflg = true;
                            break;
                        }
                    }
                    if (rflg) continue;


                    //字段里存在多个处理
                    string strFD = string.Format("[{0}]",tmr.FiledNos);
                    if (strFD.IndexOf(",") != -1)
                    {
                        strFD = string.Empty;
                        List<string> lsRCode = tmr.FiledNos.Split(',').ToList();
                        foreach (string n in lsRCode)
                        {
                            strFD += string.Format("[{0}],", n);
                        }
                        strFD = strFD.TrimEnd(',');
                    }

                    //索引创建
                    if (tmr.DTRType == "PK")
                    {
                        sqlalter += string.Format("ALTER TABLE {0} ADD CONSTRAINT [{1}] PRIMARY KEY ({2})\r\n GO\r\n",key,tmr.DTRNo,strFD);
                    }
                    else
                    {
                        sqlalter += string.Format("CREATE UNIQUE INDEX {0} ON [{1}]({2})\r\n GO\r\n", tmr.DTRNo, key, strFD);                         
                    } 
                }
                #endregion

                #region 更改索引处理

                foreach (string fdrkey in lsRupd)
                {
                    TableModelRelation tmr = dicRelations[fdrkey];
                    if (tmr == null) continue;

                    //索引名称相同，索引字段相同并且是相同索引类型，不执行
                    bool rflg = false;
                    foreach (string bpk in dicRelationsBase.Keys)
                    {
                        if (tmr.FiledNos == dicRelationsBase[bpk].FiledNos && dicRelationsBase[bpk].DTRType == tmr.DTRType && tmr.DTRNo == dicRelationsBase[bpk].DTRNo)
                        {
                            rflg = true;
                            break;
                        }
                    }
                    if (rflg) continue;


                    //字段里存在多个处理
                    string strFD = string.Format("[{0}]", tmr.FiledNos);
                    if (strFD.IndexOf(",") != -1)
                    {
                        strFD = string.Empty;
                        List<string> lsRCode = tmr.FiledNos.Split(',').ToList();
                        foreach (string n in lsRCode)
                        {
                            strFD += string.Format("[{0}],", n);
                        }
                        strFD = strFD.TrimEnd(',');
                    }

                    //todo 
                    //先删后增索引
                    sqlalter += string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]\r\n GO\r\n", key, tmr.DTRNo);
                    if (tmr.DTRType == "PK")
                    {
                        sqlalter += string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] PRIMARY KEY ({2})\r\n GO\r\n", key, tmr.DTRNo, strFD);
                    }
                    else
                    {
                        sqlalter += string.Format("CREATE UNIQUE INDEX [{0}] ON [{1}]({2})\r\n GO\r\n", tmr.DTRNo, key, strFD);
                    }
                }

                #endregion

                #region 删除索引处理
                foreach (string fdrkey in lsRdel)
                {
                    sqlalter += string.Format("ALTER TABLE [{0}] DROP CONSTRAINT [{1}]\r\n GO\r\n", key, fdrkey);
                }

                #endregion

                #region 删除字段处理
                foreach (string fdkey in lsFdel)
                {
                    sqlalter += string.Format("ALTER TABLE [{0}] DROP COLUMN [{1}]\r\n GO\r\n", key,fdkey);
                }
                #endregion

                if (string.IsNullOrEmpty(sqlalter)) continue;
                query = string.Format("--------------------------------------------{2}.修改表：{1}-开始------------------------------------------------\r\n SET ANSI_PADDING ON\r\n GO\r\n {0}\r\n GO\r\n SET ANSI_PADDING OFF\r\n GO\r\n --------------------------------------------{2}.修改表：{1}-结束------------------------------------------------\r\n", sqlalter,key,index);

                dicquery.Add(key, query);
                index++;
            }
            return dicquery;
        } 

      

        /// <summary>
        /// 新增表创建sql语句
        /// </summary>
        /// <param name="lstb"></param>
        /// <param name="dbModel"></param>
        /// <returns></returns>
        private Dictionary<string, string> CreateTBQuery(List<string> lstb,DBModel dbModel)
        {
            Dictionary<string, string> dicquery = new Dictionary<string, string>();
            int index = 0;
            foreach (string key in lstb)
            {                 
                TableModel tm = dbModel.Tables[key];
                if (tm!=null)
                {
                    Dictionary<string, TableModelField> dicFields = dbModel.Tables[key].Fields;
                    Dictionary<string, TableModelRelation> dicRelations = dbModel.Tables[key].Relations;
                    string sqlfields = string.Empty, sqlrelation = string.Empty, sqlalter = string.Empty, query = string.Empty;

                    foreach (string f in dicFields.Keys)
                    {
                        //字段构建
                        sqlfields += string.Format(" \r\n [{0}] [{1}]{2} {3}NULL,", dicFields[f].DTFNo, dicFields[f].FullDTFType,FieldDataLength(dicFields[f]),dicFields[f].IsNotNull == "0" ? "NOT " : "");

                        //字段默认值
                        if (!string.IsNullOrWhiteSpace(dicFields[f].DefaultValue))
                        {
                            sqlalter += string.Format("ALTER TABLE [dbo].[{0}] ADD  CONSTRAINT [DF_{0}_{2}] DEFAULT {1} FOR [{2}]\r\n GO\r\n", key, dicFields[f].DefaultValue, dicFields[f].DTFNo);
                        }

                        //备注说明
                        if (!string.IsNullOrWhiteSpace(dicFields[f].DTFName))
                        {
                            sqlalter += string.Format("EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'{0}' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'{1}', @level2type=N'COLUMN',@level2name=N'{2}'\r\n GO\r\n",
                                dicFields[f].DTFName, key, dicFields[f].DTFNo);
                        }
                    }

                    if (!string.IsNullOrEmpty(sqlfields))
                    {
                        sqlfields = sqlfields.TrimEnd(',');
                    }

                    string strPK = string.Empty, strAK = string.Empty;
                    foreach (string r in dicRelations.Keys)
                    {
                        string strASC = string.Format("[{0}]",dicRelations[r].FiledNos);
                        if (strASC.IndexOf(",") != -1)
                        {
                            strASC = string.Empty;
                            List <string> lsCode = dicRelations[r].FiledNos.Split(',').ToList();
                            foreach (string n in lsCode)
                            {
                                strASC += string.Format(" \r\n [{0}] ASC,", n);
                            }
                            strASC = strASC.TrimEnd(',');
                        }

                        if (dicRelations[r].DTRType == "PK")
                        {
                            strPK += string.Format("\r\n CONSTRAINT [{0}] PRIMARY KEY CLUSTERED \r\n ({1}\r\n )WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY],", dicRelations[r].DTRNo, strASC);
                        }else{
                            strAK += string.Format("\r\n CONSTRAINT [{0}] UNIQUE NONCLUSTERED \r\n ({1}\r\n )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],", dicRelations[r].DTRNo, strASC);
                        }
                    }

                    if (!string.IsNullOrEmpty(strPK))
                    {
                        strPK = strPK.TrimEnd(',');
                        sqlrelation = strPK;
                    }

                    if (!string.IsNullOrEmpty(strAK))
                    {
                        strAK = strAK.TrimEnd(',');
                        sqlrelation += string.Format("{0}{1}",string.IsNullOrEmpty(sqlrelation) ? "" : ",",strAK);
                    }

                    query = string.Format("--------------------------------------------{4}.新增表：{0}-开始------------------------------------------------\r\n SET ANSI_PADDING ON\r\n GO\r\n CREATE TABLE [dbo].[{0}]({1}{2}) ON [PRIMARY]\r\n GO\r\n SET ANSI_PADDING OFF\r\n GO\r\n {3}\r\n --------------------------------------------{4}.新增表：{0}-结束------------------------------------------------", key, sqlfields,
                        string.Format("{0}{1}",string.IsNullOrEmpty(sqlrelation) ? "" : "," , sqlrelation), sqlalter,index);

                    dicquery.Add(key, query);
                    index++;
                }
            } 
            return dicquery;
        }


        /// <summary>
        /// 以输入源为基准时才存在删除，删除目标数据库表的操作
        /// </summary>
        /// <param name="lstb"></param>
        /// <param name="dbModel"></param>
        /// <returns></returns>
        private Dictionary<string, string> DropTBQuery(List<string> lstb)
        {
            Dictionary<string, string> dicquery = new Dictionary<string, string>();
            foreach (string tbname in lstb)
            {
                string query = string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U'))\r\n DROP TABLE[dbo].[{0}]\r\n GO\r\n", tbname); 
                dicquery.Add(tbname, query); 
            }
            return dicquery;
        }
        #endregion

    }
}
